package com.chatdating.mapper;

import com.chatdating.entity.vo.MatchResultVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface MatchResultVoMapper {

    // 通过用户喜欢的标签表推荐异性
    @Select("SELECT\n" +
            "u.id as id,\n" +
            "COUNT(ut.tag_id) AS num,\n" +
            "GROUP_CONCAT(t.name SEPARATOR ', ') AS tags, \n" +
            "SUM(lt.num) AS preference_score \n" +
            "from user u \n" +
            "left join user_tag_form ut ON u.id = ut.user_id\n" +
            "left join tag_form t ON ut.tag_id = t.id \n" +
            "left join like_tag_form lt ON ut.tag_id = lt.tag_id AND lt.user_id = #{id}\n" +
            "left join watch_form wf ON u.id = wf.watch_user_id AND wf.user_id = #{id}\n" +
            "WHERE \n" +
            "u.gender != (SELECT gender FROM user WHERE id = #{id})\n" +
            "AND wf.watch_user_id IS NULL\n" +
            "AND u.deleted = 0\n" +
            "AND u.authstatus = 2\n" +
            "AND ut.tag_id IN (SELECT tag_id FROM like_tag_form WHERE user_id = #{id})\n" +
            "GROUP BY \n" +
            "u.id\n" +
            "ORDER BY\n" +
            "preference_score DESC,\n" +
            "num DESC\n" +
            "LIMIT 1;")
    MatchResultVo getMatchResultVoById(int id);


    // 通过用户自己的标签推荐异性
    @Select("SELECT\n" +
            "    u.id AS id,\n" +
            "    SUM(lt.num) AS preference_score,\n" +
            "    COUNT(ut.tag_id) AS match_count,\n" +
            "    IFNULL(GROUP_CONCAT(t.name SEPARATOR ', '), '无匹配标签') AS matched_tags\n" +
            "FROM\n" +
            "    user u\n" +
            "LEFT JOIN \n" +
            "    user_tag_form ut ON u.id = ut.user_id\n" +
            "LEFT JOIN \n" +
            "    tag_form t ON ut.tag_id = t.id\n" +
            "LEFT JOIN \n" +
            "    watch_form wf ON u.id = wf.watch_user_id AND wf.user_id = #{id}\n" +
            "LEFT JOIN \n" +
            "    like_tag_form lt ON ut.tag_id = lt.tag_id AND lt.user_id = #{id}\n" +
            "WHERE \n" +
            "    u.gender != (SELECT gender FROM user WHERE id = #{id})\n" +
            "    AND wf.watch_user_id IS NULL\n" +
            "    AND (1=1\n" +
            "         OR ut.tag_id IN (SELECT tag_id FROM user_tag_form WHERE user_id = #{id})\n" +
            "         OR ut.tag_id IN (SELECT tag_id FROM like_tag_form WHERE user_id = #{id}))\n" +
            "    AND u.deleted=0\n" +
            "    AND u.authstatus=2\n" +
            "GROUP BY \n" +
            "    u.id\n" +
            "ORDER BY \n" +
            "    preference_score DESC,\n" +
            "    match_count DESC,\n" +
            "    u.id ASC\n" +
            "LIMIT 1;\n")
    MatchResultVo getMatchResultVoBySelfTagId(int id);
}
